import logging
import re

import yaml
from cachetools import TTLCache, cached

from database.database_conf import DatabaseConf
from database.database_object import DatabaseObject
from database.mssql_object import MssqlObject
from database.mysql_object import MysqlObject
from database.oracle_object import OracleObject
from database.postgres_object import PostgresObject

cache = TTLCache(maxsize=1000, ttl=300)


def get_database_conf():
    with open('../crud_api_base/application.yml', 'r', encoding='utf-8') as file:
        config = yaml.load(file, Loader=yaml.FullLoader)
    druid = config.get("spring").get("datasource").get("druid")

    db_conf = DatabaseConf()
    db_conf.db_username = str(druid.get("username"))
    db_conf.db_password = str(druid.get("password"))

    db_url = druid.get("url")
    if "jdbc:oracle:" in db_url:
        db_conf.db_type = 0
        pattern = r'^jdbc:oracle:thin:@([^:]+):(\d+)/([^?]+)'
        match = re.match(pattern, db_url)
        db_conf.db_address = str(match.group(1))
        db_conf.db_port = int(match.group(2))
        db_conf.db_name = str(match.group(3))
    elif "jdbc:sqlserver:" in db_url:
        db_conf.db_type = 1
        pattern = r'^jdbc:sqlserver://([^:]+):(\d+);.*?DatabaseName=([^;]+)'
        match = re.match(pattern, db_url)
        db_conf.db_address = str(match.group(1))
        db_conf.db_port = int(match.group(2))
        db_conf.db_name = str(match.group(3))
    elif "jdbc:mysql:" in db_url:
        db_conf.db_type = 2
        pattern = r'^jdbc:mysql://([^:]+):(\d+)/([^?]+)'
        match = re.match(pattern, db_url)
        db_conf.db_address = str(match.group(1))
        db_conf.db_port = int(match.group(2))
        db_conf.db_name = str(match.group(3))
    elif "jdbc:postgresql:" in db_url:
        db_conf.db_type = 6
        pattern = r'^jdbc:postgresql://([^:]+):(\d+)/([^?]+)'
        match = re.match(pattern, db_url)
        db_conf.db_address = str(match.group(1))
        db_conf.db_port = int(match.group(2))
        db_conf.db_name = str(match.group(3))
    else:
        logging.error(f"未支持的数据库类型[{db_url}]")
        raise Exception(f"未支持的数据库类型[{db_url}]")
    return db_conf


def _get_database_object(db_conf: DatabaseConf) -> DatabaseObject:
    try:
        if db_conf.db_type == 0:
            database_object = OracleObject()
        elif db_conf.db_type == 1:
            database_object = MssqlObject()
        elif db_conf.db_type == 2:
            database_object = MysqlObject()
        elif db_conf.db_type == 6:
            database_object = PostgresObject()
        else:
            raise Exception(f"未支持的数据库类型[{db_conf.db_type}]")

        ret = database_object.connect(db_conf)
        if not ret:
            raise Exception(f"数据库连接失败[{database_object.last_error}]")
    except Exception as ex:
        logging.error(ex)
        raise ex

    return database_object


def _get_crud_database_object() -> DatabaseObject:
    db_conf = get_database_conf()

    return _get_database_object(db_conf)


@cached(cache)
def get_schema_list() -> dict:
    db_object = None
    try:
        db_object = _get_crud_database_object()
        schema_list = db_object.get_schema_list()
        data = []
        for schema in schema_list:
            data.append({"value": schema, "label": schema})
        res = {"status": 0, "message": "success", "data": data}
    except Exception as ex:
        logging.info(str(ex))
        res = {"status": 1, "message": str(ex), "data": []}
    finally:
        if db_object:
            db_object.close()
    return res


@cached(cache)
def get_table_list(schema_name: str) -> dict:
    db_object = None
    try:
        db_object = _get_crud_database_object()
        table_list = db_object.get_table_list(schema_name)
        data = []
        for table in table_list:
            data.append({"value": table, "label": table})
        res = {"status": 0, "message": "success", "data": data}
    except Exception as ex:
        logging.info(str(ex))
        res = {"status": 1, "message": str(ex), "data": []}
    finally:
        if db_object:
            db_object.close()
    return res


@cached(cache)
def get_field_list(schema_name: str, table_name: str) -> dict:
    db_object = None
    try:
        db_object = _get_crud_database_object()
        filed_list = db_object.get_filed_list(schema_name, table_name)
        data = []
        for filed in filed_list:
            filed["value"] = filed["field_name"]
            filed["label"] = filed["field_name"]
            data.append(filed)
        res = {"status": 0, "message": "success", "data": data}
    except Exception as ex:
        logging.info(str(ex))
        res = {"status": 1, "message": str(ex), "data": []}
    finally:
        if db_object:
            db_object.close()
    return res
